package dao;

import java.sql.SQLException;
import java.util.List;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import po.City;
import po.DataSourceFactory;

/**
 * Create By IntelliJ IDEA
 *
 * @author: jsonor
 * @create-Time: 2017/10/17 8:21
 * @description: 对city表的数据库原始操作
 */
public class CityDao {

  private QueryRunner runner;

  public CityDao() {
    runner = new QueryRunner(DataSourceFactory.getDataSource());
  }

  /**
   * 查询数据表city中的所有数据
   *
   * @return 数据的集合对象
   */
  public List<City> findAll() throws SQLException {
    String sql = "select * from city";

    return runner.query(sql, new BeanListHandler<City>(City.class));
  }

  /**
   * 通过id将city中对应的数据项删除
   *
   * @param id 被删除信息的id
   * @return 被操作的数据的数数量
   */
  public int deleteSingleById(int id) throws SQLException {
    String sql = "delete from city where id = ?";

    return runner.update(sql, id);
  }

  /**
   * 通过id 查询city
   *
   * @param id 需要查询的city的id
   * @return city数据
   */
  public City findById(int id) throws SQLException {
    String sql = "select * from city where id = ?";

    return runner.query(sql, new BeanHandler<City>(City.class), id);
  }

  /**
   * 根据id更新数据表city中的数据
   *
   * @param city city
   * @return 更新的数据数量
   */
  public int updateById(City city) throws SQLException {
    String sql = "update city set name = ?, countryCode = ?, "
        + "district = ?, population = ? where id = ?";

    return runner.update(sql, city.getName(), city.getCountryCode(),
        city.getDistrict(), city.getPopulation(), city.getId());
  }

  /**
   * 添加城市信息
   *
   * @param city city
   * @return 添加的数据数量
   */
  public int add(City city) throws SQLException {
    String sql = "insert into city(name, countryCode, district, population) values(?,?,?,?)";
    return runner.update(sql, city.getName(), city.getCountryCode(),
        city.getDistrict(), city.getPopulation());
  }


  /**
   * 通过countryCode查询城市信息
   *
   * @param countryCode 城市代码
   * @return 城市信息的集合
   */
  public List<City> findByCountryCode(String countryCode) throws SQLException {
    String sql = "select * from city where countryCode like ?";

    // 如果需要模糊查询，则需要将整个查询内容拼接成一个字符串
    String param = "%" + countryCode + "%";
    return runner.query(sql, new BeanListHandler<City>(City.class), param);
  }

  /**
   * 查询有限数量的城市信息
   *
   * @param start 需要查询的城市的起始索引号
   * @param count 城市数量
   * @return 城市信息
   */
  public List<City> findLimit(int start, int count) throws SQLException {
    String sql = "select * from city limit ?, ?";
    return runner.query(sql, new BeanListHandler<City>(City.class), start, count);
  }

  /**
   * 获取city表格中的信息条数
   *
   * @return 总数
   */
  public int cityCount() throws SQLException {
    String sql = "select count(*) from city";
    Object o = runner.query(sql, new ScalarHandler());

    return Integer.parseInt(String.valueOf(o));
  }
}
